package org.lanqiao.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import org.lanqiao.pojo.CartListPOJO;
import org.lanqiao.pojo.CategoryPOJO;
import org.lanqiao.pojo.OrderListPOJO;
import org.lanqiao.pojo.PageModel;
import org.lanqiao.pojo.ProductPOJO;
import org.lanqiao.pojo.UserPOJO;
import org.lanqiao.util.SqlUtil;
import org.lanqiao.util.StringUtil;

import javafx.css.StyleableStringProperty;

public class AdminDaoImpl {
	/**
	 * 查询所有用户信息
	 * @return
	 */
	public List users() {
		UserPOJO des=null;
		List str1=new ArrayList();
		String sql="select * from user";
		try {
			List<LinkedHashMap<String,Object>> maps=SqlUtil.select21(sql);
			if(maps!=null && maps.size()>0){
				for(HashMap<String,Object> map :maps) {
					des =new UserPOJO();
					des.setUserId(map.get("USER_ID")==null?null:map.get("USER_ID").toString());
					des.setUserName(map.get("USER_NAME")==null?null:map.get("USER_NAME").toString());
					des.setAge(map.get("USER_PASSWORD")==null?null:map.get("USER_PASSWORD").toString());
					des.setSex(map.get("USER_SEX")==null?null:map.get("USER_SEX").toString());
					des.setAge(map.get("USER_AGE")==null?null:map.get("USER_AGE").toString());
					des.setTelphone(map.get("USER_TELPHONE")==null?null:map.get("USER_TELPHONE").toString());
					des.setAddres(map.get("USER_ADDRESS")==null?null:map.get("USER_ADDRESS").toString());
				str1.add(des);
				}
				}
			
		} catch (Exception e) {
		  throw new RuntimeException(e);	 
		}
		return str1;
	}
	/**
	 * 登录用户
	 */
	public UserPOJO loginUser(String name,String password) {
		UserPOJO user=null;
		String sql="select *from manager where USERNAME=? and PASSWORD=?";	
		try {
			List<HashMap<String,Object>> maps=SqlUtil.select21(sql,name,password);
			if(maps!=null && maps.size()>0){
				HashMap<String,Object> map=maps.get(0);
				user =new UserPOJO();
				user.setUserId(map.get("ID")==null?null:map.get("ID").toString());
				user.setUserName(map.get("USERNAME")==null?null:map.get("USERNAME").toString());
				user.setPassword(map.get("PASSWORD")==null?null:map.get("PASSWORD").toString());
//				user.setPhoto(map.get("PASSWORD")==null?null:map.get("PASSWORD").toString());
			}
		} catch (Exception e) {
		  throw new RuntimeException(e+"用户不匹配");	 
		}
		return user;
	}
	
	/**
	 * 删除用户
	 */
	public int delete(String id) {
		int a=0;
		try {
			String sql="delete from user where USER_ID=?";	
			a=SqlUtil.update(sql,id);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return a;
	}
	/**
	 * 修改用户
	 */
	public int amend(UserPOJO user) {
		int a=0;
		try {
			String sql="UPDATE user set USER_NAME = ?,USER_PASSWORD=?,USER_SEX=?,USER_AGE=?,USER_ADDRESS=? WHERE USER_ID =?";	
			a=SqlUtil.update(sql,user.getUserName(),user.getPassword(),user.getSex(),user.getAge(),user.getAddres(),user.getUserId());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
		return a;
	}
	/**
	 * 添加用户
	 */
	public int adduser(UserPOJO user) {
		int a=0;
		try {
			String sql="Insert into user(USER_ID,USER_NAME,USER_PASSWORD,USER_SEX,USER_AGE,USER_BIRTHDAY,USER_PHOTO,USER_TELPHONE,USER_ADDRESS,USER_COMMENT,USER_STATUS,USER_DATE_TIME)values(1,?,?,?,?,?,?,?,?,?,1,NOW())";	
			a=SqlUtil.update(sql,user.getUserName(),user.getPassword(),user.getSex(),user.getAge(),user.getAddres(),user.getUserId());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
		return a;
	}
	/**
	 * 查询商品
	 */
	public List products() {
		ProductPOJO des=null;
		List str1=new ArrayList();
		String sql="select * from product";
		try {
			List<LinkedHashMap<String,Object>> maps=SqlUtil.select21(sql);
			if(maps!=null && maps.size()>0){
				for(HashMap<String,Object> map :maps){
					des =new ProductPOJO();
					des.setProductName(map.get("PRODUCT_NAME")==null?null:map.get("PRODUCT_NAME").toString());
					des.setProductCode(map.get("PRODUCT_CODE")==null?null:map.get("PRODUCT_CODE").toString());
					des.setPrice(map.get("PRODUCT_PRICE")==null?null:map.get("PRODUCT_PRICE").toString());
					des.setPhoto(map.get("PRODUCT_PHOTO")==null?null:map.get("PRODUCT_PHOTO").toString());
					des.setDateTime(map.get("PRODUCT_DATE_TIME")==null?null:map.get("PRODUCT_DATE_TIME").toString());
					des.setSale(map.get("PRODUCT_IS_SALE")==null?null:map.get("PRODUCT_IS_SALE").toString());
					des.setStatus(map.get("PRODUCT_STATUS")==null?null:map.get("PRODUCT_STATUS").toString());
				str1.add(des);
				}
				}
			
		} catch (Exception e) {
		  throw new RuntimeException(e);	 
		}
		return str1;
	}
	/**
	 * 添加商品
	 */
	public int addProduct(ProductPOJO pro) {
		int a=0;
		try {
			String sql="Insert into product(PRODUCT_ID,PRODUCT_CODE,PRODUCT_NAME,PRODUCT_COLOR,PRODUCT_STANDARD,PRODUCT_UNIT,PRODUCT_PRICE,PRODUCT_CATEGORY_ID,PRODUCT_BRAND_ID,PRODUCT_PHOTO,PRODUCT_IS_SALE,PRODUCT_IS_LACK,PRODUCT_STATUS,PRODUCT_SUGGEST_PRICE,PRODUCT_COMMENT,PRODUCT_DATE_TIME)values(1,?,?,?,?,?,?,?,?,?,1,1,1,?,?,NOW())";	
			a=SqlUtil.update(sql,pro.getProductCode(),pro.getProductName(),pro.getProductColor(),pro.getStandard(),pro.getUnit(),pro.getPrice(),pro.getCategoryId(),pro.getBrandId(),pro.getPhoto(),pro.getSuggestPrice(),pro.getProductComment());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
		return a;
	}
	/**
	 * 修改商品
	 */
	public int amendProduct(ProductPOJO pro) {
		int a=0;
		try {
			String sql="UPDATE product set PRODUCT_NAME = ?,PRODUCT_CODE=?,PRODUCT_PRICE=?,PRODUCT_IS_SALE=?,PRODUCT_STATUS=? where PRODUCT_ID=?";	
			a=SqlUtil.update(sql,pro.getProductName(),pro.getProductCode(),pro.getPrice(),pro.getSale(),pro.getStatus(),pro.getProductId());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
		return a;
	}
	/**
	 * 删除商品
	 */
	public int deleteProduct(String id) {
		int a=0;
		try {
			String sql="delete from product where PRODUCT_ID=?";	
			a=SqlUtil.update(sql,id);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return a;
	}
	
	/**
	 * 分页查询用户
	 */
	public PageModel<List> list(String page,String pageSize){
		String sql="select * from user";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<UserPOJO> list2=new ArrayList<UserPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				UserPOJO des =new UserPOJO();
				des.setUserId(StringUtil.valueOf(map.get("USER_ID")));
				des.setUserName(StringUtil.valueOf(map.get("USER_NAME")));
				des.setPassword(StringUtil.valueOf(map.get("USER_PASSWORD")));
				des.setSex(StringUtil.valueOf(map.get("USER_SEX")));
				des.setAge(StringUtil.valueOf(map.get("USER_AGE")));
				des.setTelphone(StringUtil.valueOf(map.get("USER_TELPHONE")));
				des.setAddres(StringUtil.valueOf(map.get("USER_ADDRESS")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	
	/**
	 * 分页查询商品
	 */
	public PageModel<List> listProduct(String page,String pageSize){
		String sql="select * from product";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductId(StringUtil.valueOf(map.get("PRODUCT_ID")));
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("PRODUCT_STATUS")));
				des.setSale(StringUtil.valueOf(map.get("PRODUCT_IS_SALE")));
				des.setPhoto(StringUtil.valueOf(map.get("PRODUCT_PHOTO")));
				des.setDateTime(StringUtil.valueOf(map.get("PRODUCT_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 分页查询订单
	 */
	public PageModel<List> listOrder(String page,String pageSize){
		String sql="select * from order_list";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		OrderListPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<OrderListPOJO> list2=new ArrayList<OrderListPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new OrderListPOJO();
				des.setId(StringUtil.valueOf(map.get("ORDER_ID")));
				des.setUserId(StringUtil.valueOf(map.get("ORDER_USER_ID")));
				des.setComment(StringUtil.valueOf(map.get("ORDER_COMMENT")));
				des.setTotalPrice(StringUtil.valueOf(map.get("ORDER_TOTAL_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("ORDER_STATUS")));
				des.setDstatus(StringUtil.valueOf(map.get("ORDER_DSTATUS")));
				des.setDatetime(StringUtil.valueOf(map.get("ORDER_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	
	/**
	 * 删除订单
	 */
	public int deleteOrder(String id) {
		int a=0;
		try {
			String sql="delete from order_list where ORDER_ID=?";
			a=SqlUtil.update(sql, id);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return a;
	}
	/**
	 * 模糊搜索商品
	 */
	public PageModel<List> moProduct(String name,String page,String pageSize){
		 name ="'%"+name+"%'"; 
		String sql="select * from product where PRODUCT_NAME like"+name;
//		System.out.println(name);
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductId(StringUtil.valueOf(map.get("PRODUCT_ID")));
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("PRODUCT_STATUS")));
				des.setSale(StringUtil.valueOf(map.get("PRODUCT_IS_SALE")));
				des.setPhoto(StringUtil.valueOf(map.get("PRODUCT_PHOTO")));
				des.setDateTime(StringUtil.valueOf(map.get("PRODUCT_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 模糊搜索用户
	 */
	public PageModel<List> moUser(String name,String page,String pageSize){
		 name ="'%"+name+"%'"; 
		String sql="select * from user where USER_NAME like"+name;
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<UserPOJO> list2=new ArrayList<UserPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				UserPOJO des =new UserPOJO();
				des.setUserId(StringUtil.valueOf(map.get("USER_ID")));
				des.setUserName(StringUtil.valueOf(map.get("USER_NAME")));
				des.setPassword(StringUtil.valueOf(map.get("USER_PASSWORD")));
				des.setSex(StringUtil.valueOf(map.get("USER_SEX")));
				des.setAge(StringUtil.valueOf(map.get("USER_AGE")));
				des.setTelphone(StringUtil.valueOf(map.get("USER_TELPHONE")));
				des.setAddres(StringUtil.valueOf(map.get("USER_ADDRESS")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 查询商品类别
	 */
	public PageModel<List> Categoryquery(String page,String pageSize){
		String sql="select * from category";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		CategoryPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<CategoryPOJO> list2=new ArrayList<CategoryPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new CategoryPOJO();
				des.setId(StringUtil.valueOf(map.get("CATEGORY_ID")));
				des.setName(StringUtil.valueOf(map.get("CATEGORY_NAME")));
				des.setOrder(StringUtil.valueOf(map.get("CATEGORY_ORDER")));
				des.setStatus(StringUtil.valueOf(map.get("CATEGORY_STATUS")));
				des.setDatetime(StringUtil.valueOf(map.get("CATEGORY_DATA_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 修改商品类别
	 */
	public int modifying(CategoryPOJO cate) {
		int a=0;
		try {
			String sql="UPDATE category set CATEGORY_NAME=?,CATEGORY_ORDER=?,CATEGORY_STATUS=?,CATEGORY_DATA_TIME=NOW() where CATEGORY_ID=?";	
			a=SqlUtil.update(sql,cate.getName(),cate.getOrder(),cate.getStatus(),cate.getId());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
		return a;
	}
	/**
	 * 添加商品类别
	 */
	public int addcategory(CategoryPOJO cate) {
		int a=0;
		try {
			String sql="Insert into category(CATEGORY_ID,CATEGORY_NAME,CATEGORY_ORDER,CATEGORY_STATUS,CATEGORY_DATA_TIME)values(?,?,?,?,NOW()) ";
			a=SqlUtil.update(sql,cate.getId(),cate.getName(),cate.getOrder(),cate.getStatus());
		} catch (Exception e) {
			// TODO: handle exception
		}
		return a;
	}
	/**
	 * 删除商品类别
	 */
	public int deletecategory(String id) {
		int a=0;
		try {
			String sql="delete from category where CATEGORY_ID=?";
			a=SqlUtil.update(sql,id);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return a;
	}
	/**
	 * 查询购物车
	 */
	public  PageModel<List> inquiringShopping(String page,String pageSize){
		String sql="select * from car_list";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		CartListPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<CartListPOJO> list2=new ArrayList<CartListPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new CartListPOJO();
				des.setId(StringUtil.valueOf(map.get("CAR_ID")));
				des.setUserId(StringUtil.valueOf(map.get("CAR_USER_ID")));
				des.setProductId(StringUtil.valueOf(map.get("CAR_PRODUCT_ID")));
				des.setProductCount(StringUtil.valueOf(map.get("CAR_PRODUCT_COUNT")));
				des.setTotalPrice(StringUtil.valueOf(map.get("CAR_TOTAL_PRICE")));
				des.setOrderId(StringUtil.valueOf(map.get("CAR_ORDER_ID")));
				des.setStatus(StringUtil.valueOf(map.get("CAR_STATUS")));
				des.setDatetiem(StringUtil.valueOf(map.get("CAR_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 根据购物车ID删除购物车商品
	 */
	public int deleteShopping(String id) {
		int a=0;
		try {
			String sql="delete from car_list where CAR_ID=?";
			a=SqlUtil.update(sql,id);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return a;
	}
	/**
	 * 删除购物车状态为0，且没有加入订单的商品
	 */
	public int deleteShoppings() {
		int a=0;
		try {
			String sql="delete from car_list where CAR_STATUS='0' and CAR_ORDER_ID is NULL";
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return a;
	}
	/**
	 * 查询商品销量前十的产品
	 */
	public PageModel<List> ShiProduct(String page,String pageSize){
		String sql="SELECT PRODUCT_NAME,PRODUCT_CODE, COUNT(PRODUCT_CODE) product_sales,PRODUCT_PRICE,PRODUCT_PRICE*COUNT( PRODUCT_CODE) product_pay FROM order_list O LEFT JOIN car_list C ON O.ORDER_ID = C.CAR_ORDER_ID LEFT JOIN product p ON C.CAR_PRODUCT_ID = p.PRODUCT_ID GROUP BY PRODUCT_CODE,PRODUCT_NAME ORDER BY COUNT(PRODUCT_CODE)DESC";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setSale(StringUtil.valueOf(map.get("product_sales")));
				des.setSaleCount(StringUtil.valueOf(map.get("product_pay")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 *商品类别销售量
	 */
	public PageModel<List> leiProduct(String page,String pageSize){
		String sql="SELECT CATEGORY_ID,CATEGORY_NAME,COUNT( PRODUCT_CODE) CATEGORY_SALES,PRODUCT_PRICE*COUNT(PRODUCT_CODE) CATEGORY_PAY FROM order_list O LEFT JOIN car_list C ON O.ORDER_ID = C.CAR_ORDER_ID LEFT JOIN product p ON C.CAR_PRODUCT_ID = p.PRODUCT_ID LEFT JOIN category g ON p.PRODUCT_CATEGORY_ID = g.CATEGORY_ID GROUP BY CATEGORY_ID ORDER BY COUNT(PRODUCT_CODE) DESC";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		CategoryPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<CategoryPOJO> list2=new ArrayList<CategoryPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new CategoryPOJO();
				des.setId(StringUtil.valueOf(map.get("CATEGORY_ID")));
				des.setName(StringUtil.valueOf(map.get("CATEGORY_NAME")));
				des.setOrder(StringUtil.valueOf(map.get("CATEGORY_SALES")));
				des.setStatus(StringUtil.valueOf(map.get("CATEGORY_PAY")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 按照时间查询购物车
	 */
	public  PageModel<List> timeShopping(String page,String pageSize){
		String sql="select * from car_list order by CAR_DATE_TIME desc";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		CartListPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<CartListPOJO> list2=new ArrayList<CartListPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new CartListPOJO();
				des.setId(StringUtil.valueOf(map.get("CAR_ID")));
				des.setUserId(StringUtil.valueOf(map.get("CAR_USER_ID")));
				des.setProductId(StringUtil.valueOf(map.get("CAR_PRODUCT_ID")));
				des.setProductCount(StringUtil.valueOf(map.get("CAR_PRODUCT_COUNT")));
				des.setTotalPrice(StringUtil.valueOf(map.get("CAR_TOTAL_PRICE")));
				des.setOrderId(StringUtil.valueOf(map.get("CAR_ORDER_ID")));
				des.setStatus(StringUtil.valueOf(map.get("CAR_STATUS")));
				des.setDatetiem(StringUtil.valueOf(map.get("CAR_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 按照时间查询订单
	 */
	public PageModel<List> timeOrder(String page,String pageSize){
		String sql="select * from order_list order by ORDER_DATE_TIME desc";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		OrderListPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<OrderListPOJO> list2=new ArrayList<OrderListPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new OrderListPOJO();
				des.setId(StringUtil.valueOf(map.get("ORDER_ID")));
				des.setUserId(StringUtil.valueOf(map.get("ORDER_USER_ID")));
				des.setStatus(StringUtil.valueOf(map.get("ORDER_STATUS")));
				des.setComment(StringUtil.valueOf(map.get("ORDER_COMMENT")));
				des.setTotalPrice(StringUtil.valueOf(map.get("ORDER_TOTAL_PRICE")));
				des.setDatetime(StringUtil.valueOf(map.get("ORDER_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 按时间查询商品
	 */
	public PageModel<List> timeProduct(String page,String pageSize){
		String sql="select * from product order by PRODUCT_DATE_TIME desc";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductId(StringUtil.valueOf(map.get("PRODUCT_ID")));
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("PRODUCT_STATUS")));
				des.setSale(StringUtil.valueOf(map.get("PRODUCT_IS_SALE")));
				des.setPhoto(StringUtil.valueOf(map.get("PRODUCT_PHOTO")));
				des.setDateTime(StringUtil.valueOf(map.get("PRODUCT_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 按时间查询用户
	 */
	public PageModel<List> timelist(String page,String pageSize){
		String sql="select * from user order by USER_DATE_TIME desc";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<UserPOJO> list2=new ArrayList<UserPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				UserPOJO des =new UserPOJO();
				des.setUserId(StringUtil.valueOf(map.get("USER_ID")));
				des.setUserName(StringUtil.valueOf(map.get("USER_NAME")));
				des.setPassword(StringUtil.valueOf(map.get("USER_PASSWORD")));
				des.setSex(StringUtil.valueOf(map.get("USER_SEX")));
				des.setAge(StringUtil.valueOf(map.get("USER_AGE")));
				des.setTelphone(StringUtil.valueOf(map.get("USER_TELPHONE")));
				des.setAddres(StringUtil.valueOf(map.get("USER_ADDRESS")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.valueOf(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 按照类别查询商品
	 */
	public PageModel<List> codeProduct(String name,String page,String pageSize){
		name ="'%"+name+"%'"; 
		String sql="select * from category c  LEFT JOIN product p  on c.CATEGORY_ID=p.PRODUCT_CATEGORY_ID where c.CATEGORY_NAME like"+name;
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductId(StringUtil.valueOf(map.get("PRODUCT_ID")));
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("PRODUCT_STATUS")));
				des.setSale(StringUtil.valueOf(map.get("PRODUCT_IS_SALE")));
				des.setPhoto(StringUtil.valueOf(map.get("PRODUCT_PHOTO")));
				des.setDateTime(StringUtil.valueOf(map.get("PRODUCT_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
	/**
	 * 清空订单中被用户删除的数据
	 */
	public int Emptyorder() {
		int a=0;
		try {
			String sql="delete from order_list where ORDER_DSTATUS=0";
			a=SqlUtil.update(sql);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return a;
	}
	/**
	 * 商品上下架
	 */
	public int modifyState(String id) {
		int a=0;
		try {
			String sql="UPDATE product set PRODUCT_STATUS=0 where PRODUCT_ID= ?";
			a=SqlUtil.update(sql,id);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return a;
	}
	/**
	 * 根据分类Id
	 */
	public List selectcate(String id) {
		String sql=null;
		CategoryPOJO des=null;
			List str=new ArrayList();
			String sql1="select * from category where CATEGORY_ID=?";
			List<HashMap<String,Object>> maps1=SqlUtil.select21(sql1,id);
			if(maps1!=null && maps1.size()>0) {
				for(HashMap<String,Object> map :maps1) {
					des =new CategoryPOJO();
					des.setId(map.get("CATEGORY_ID")==null?null:map.get("CATEGORY_ID").toString());
					des.setName(map.get("CATEGORY_NAME")==null?null:map.get("CATEGORY_NAME").toString());
					des.setOrder(map.get("CATEGORY_ORDER")==null?null:map.get("CATEGORY_ORDER").toString());
					des.setStatus(map.get("CATEGORY_STATUS")==null?null:map.get("CATEGORY_STATUS").toString());
					des.setDatetime(map.get("CATEGORY_DATA_TIME")==null?null:map.get("CATEGORY_DATA_TIME").toString());
					str.add(des);
				}
			}
			return str;
	}
	/**
	 * 按照时间顺序
	 */
	public PageModel<List> priceProduct(String page,String pageSize){
		String sql="select * from product order by PRODUCT_PRICE desc";
		PageModel pageModel=new PageModel<>(sql,page,pageSize);
		ProductPOJO des=null;
		List<HashMap<String,Object>> list=SqlUtil.select21(pageModel.toMysqlSql());
		List<ProductPOJO> list2=new ArrayList<ProductPOJO>();
		if(list.size()>0) {
			for(HashMap<String,Object> map: list) {
				des =new ProductPOJO();
				des.setProductId(StringUtil.valueOf(map.get("PRODUCT_ID")));
				des.setProductName(StringUtil.valueOf(map.get("PRODUCT_NAME")));
				des.setProductCode(StringUtil.valueOf(map.get("PRODUCT_CODE")));
				des.setPrice(StringUtil.valueOf(map.get("PRODUCT_PRICE")));
				des.setStatus(StringUtil.valueOf(map.get("PRODUCT_STATUS")));
				des.setSale(StringUtil.valueOf(map.get("PRODUCT_IS_SALE")));
				des.setPhoto(StringUtil.valueOf(map.get("PRODUCT_PHOTO")));
				des.setDateTime(StringUtil.valueOf(map.get("PRODUCT_DATE_TIME")));
			list2.add(des);
			}
		}
//		把list转换为对象
		pageModel.setList(list2);
		List<HashMap<String,Object>> countList=SqlUtil.select21(pageModel.toCountSql());
		String total=StringUtil.empty(countList.get(0).get("count"));
		pageModel.setTotal(Integer.valueOf(total));
		pageModel.setTotalPage(pageModel.getTotalPage());
		return pageModel;
	}
}
